﻿using System;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Configuration;
using System.Collections.Generic;
using System.Collections;
using PersistentObject.Common.Criteria;
using PersistentObject.SqlServer2012;
using System.Data;
using System.Linq;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using PersistentObject.StudentTest.Entities;
using PersistentObject.Common;
using PersistentObject.Common.Sorts;

namespace PersistentObject.StudentTest
{
    public abstract class BaseOrmTest
    {
        public abstract BaseDialect Dialect { get; }
        public POConnection Connection { get; set; }
        static string IMAGE_PATH = @"D:\logo.png";

        public abstract string ConfigName { get; }

        public BaseOrmTest()
        {
            string ConnectionString = ConfigurationManager.ConnectionStrings[ConfigName].ConnectionString;
            Connection = new POConnection(ConnectionString, Dialect);
        }

        [TestMethod]
        public void LoadWithoutCriteria()
        {
            var r1 = Connection.Load<ClassA>(null, new OrderBy[] { new OrderByProperty("Id") });
            Assert.AreEqual(r1.Count() > 0, true);

            var ra = r1.First();
            Assert.AreEqual(ra.BOOL == true, true);
            Assert.AreEqual(ra.INT == 10, true);
            Assert.AreEqual(ra.STRING == "A", true);
            Assert.AreEqual(ra.DATETIME == new DateTime(2013, 1, 1), true);
            Assert.AreEqual(ra.DECIMAL == 10.01M, true);

            Assert.AreEqual(ra.ClassBId.BOOL == true, true);
            Assert.AreEqual(ra.ClassBId.INT == ra.ClassBId.Id + 10, true);
            Assert.AreEqual(ra.ClassBId.STRING == "B1", true);
            Assert.AreEqual(ra.ClassBId.DATETIME == new DateTime(2013, 1, 1), true);
            Assert.AreEqual(ra.ClassBId.DECIMAL == ra.ClassBId.Id + 0.01M, true);

            Assert.AreEqual(ra.ClassBId2.BOOL == true, true);
            Assert.AreEqual(ra.ClassBId2.INT == ra.ClassBId2.Id + 10, true);
            Assert.AreEqual(ra.ClassBId2.STRING == "BB1", true);
            Assert.AreEqual(ra.ClassBId2.DATETIME == new DateTime(2013, 1, 1), true);
            Assert.AreEqual(ra.ClassBId2.DECIMAL == ra.ClassBId2.Id + 0.01M, true);

        }

        [TestMethod]
        public void LoadPagingWithCriteria()
        {
            BinaryCriteria criteria = new BinaryCriteria("Id", 3, BinaryComparison.GreaterThan);
            OrderBy[] orderBys = new OrderBy[]{
                new OrderByProperty("Id")
            };
            var r1 = Connection.Load<Student>(criteria, orderBys, 5, 1);
            Assert.AreEqual(r1.Count() > 0, true);

            var ra = r1.First();
            Assert.AreEqual(ra.Id == 9, true);
            Assert.AreEqual(ra.FirstName == "Ross", true);
            Assert.AreEqual(ra.LastName == "Geller", true);

            var r2 = Connection.Load<Student>(null, null, 5, 1);
            Assert.AreEqual(r2.Count() > 0, true);
            var rb = r2.First();

            Assert.AreEqual(rb.Id == 6, true);
            Assert.AreEqual(rb.FirstName == "Mary", true);
            Assert.AreEqual(rb.LastName == "Bangert", true);
        }

        [TestMethod]
        public void CountWithCriteria()
        {
            BaseCriteria c1 = new BinaryCriteria("FirstName", "Bill");
            int r1 = Connection.Count<Student>(c1);
            Assert.AreEqual(r1 > 0, true);

            int r2 = Connection.Count<Student>();
            Assert.AreEqual(r2 > 0, true);
        }


        [TestMethod]
        public void LoadWithBinaryCriteria()
        {
            BaseCriteria c1 = new BinaryCriteria("FirstName", "Bill");
            var r1 = Connection.Load<Student>(c1, null);
            Assert.AreEqual(r1.First() != null, true);

            BaseCriteria c2 = new BinaryCriteria("LastName", "Geller");
            var r2 = Connection.Load<Student>(c2, null);
            Assert.AreEqual(r2.Count() > 0, true);
            var s2 = r2.First();
            Assert.AreEqual(s2.LastName == "Geller", true);

            BaseCriteria c3 = new BinaryCriteria("Tutor.FirstName", "David");
            var r3 = Connection.Load<Teacher>(c3, null);
            Assert.AreEqual(r3.Count() > 0, true);
            var s3 = r3.First();
            Assert.AreEqual(s3.Tutor.FirstName == "David", true);

            BaseCriteria c4 = new BinaryCriteria("Tutor.Gender.Name", "Male");
            var r4 = Connection.Load<Teacher>(c4, null);
            Assert.AreEqual(r4.Count() > 0, true);
            var s4 = r4.First();
            Assert.AreEqual(s4.Tutor.Gender.Name == "Male", true);
        }

        [TestMethod]
        public void LoadWithBinaryCriteriaByNotEqual()
        {
            BaseCriteria c1 = new BinaryCriteria("FirstName", "Bill", BinaryComparison.NotEqual);
            var r1 = Connection.Load<Student>(c1, null);
            Assert.AreEqual(r1.First() != null, true);

            BaseCriteria c2 = new BinaryCriteria("LastName", "Geller", BinaryComparison.NotEqual);
            var r2 = Connection.Load<Student>(c2, null);
            Assert.AreEqual(r2.Count() > 0, true);

            BaseCriteria c3 = new BinaryCriteria("Tutor.FirstName", "David", BinaryComparison.NotEqual);
            var r3 = Connection.Load<Teacher>(c3, null);
            Assert.AreEqual(r3.Count() > 0, true);

            BaseCriteria c4 = new BinaryCriteria("Tutor.Gender.Name", "Male", BinaryComparison.NotEqual);
            var r4 = Connection.Load<Teacher>(c4, null);
            Assert.AreEqual(r4.Count() > 0, true);

            var x = new BinaryCriteria(new OperandValue("John"), new OperandProperty("FirstName"));
            var y = new BetweenCriteria("SnapTime", new OperandProperty("StartTime"), new OperandProperty("EndTime"));
            var z = new ExistsCriteria(typeof(Student), new BinaryCriteria("ProductId.Name", "Adidas"), new ExistsLink[]{ new ExistsLink("Id", "Sales.Customer.Id")});
        }

        [TestMethod]
        public void LoadWithBinaryCriteriaByGreaterThan()
        {
            BaseCriteria c1 = new BinaryCriteria("Birthday", new DateTime(1991, 1, 1), BinaryComparison.GreaterThan);
            var r1 = Connection.Load<Student>(c1);
            Assert.AreEqual(r1.Count() > 0, true);

            BaseCriteria c2 = new BinaryCriteria("Id", 10, BinaryComparison.GreaterThan);
            var r2 = Connection.Load<Student>(c2);
            Assert.AreEqual(r2.Count() > 0, true);
        }

        [TestMethod]
        public void LoadWithBinaryCriteriaByGreaterThanOrEqual()
        {
            BaseCriteria c1 = new BinaryCriteria("Birthday", new DateTime(1991, 1, 1), BinaryComparison.GreaterThanOrEqual);
            var r1 = Connection.Load<Student>(c1);
            Assert.AreEqual(r1.Count() > 0, true);

            BaseCriteria c2 = new BinaryCriteria("Id", 10, BinaryComparison.GreaterThanOrEqual);
            var r2 = Connection.Load<Student>(c2);
            Assert.AreEqual(r2.Count() > 0, true);
        }

        [TestMethod]
        public void LoadWithBinaryCriteriaByLessThan()
        {
            BaseCriteria c1 = new BinaryCriteria("Birthday", new DateTime(1991, 1, 1), BinaryComparison.LessThan);
            var r1 = Connection.Load<Student>(c1);
            Assert.AreEqual(r1.Count() > 0, true);

            BaseCriteria c2 = new BinaryCriteria("Id", 10, BinaryComparison.LessThan);
            var r2 = Connection.Load<Student>(c2);
            Assert.AreEqual(r2.Count() > 0, true);
        }

        [TestMethod]
        public void LoadWithBinaryCriteriaByLessThanOrEqual()
        {
            BaseCriteria c1 = new BinaryCriteria("Birthday", new DateTime(1991, 1, 1), BinaryComparison.LessThanOrEqual);
            var r1 = Connection.Load<Student>(c1);
            Assert.AreEqual(r1.Count() > 0, true);

            BaseCriteria c2 = new BinaryCriteria("Id", 10, BinaryComparison.LessThanOrEqual);
            var r2 = Connection.Load<Student>(c2);
            Assert.AreEqual(r2.Count() > 0, true);
        }

        [TestMethod]
        public void LoadWithBinaryCriteriaByBeginWith()
        {
            BaseCriteria c1 = new BinaryCriteria("FirstName", "R", BinaryComparison.BeginWith);
            var r1 = Connection.Load<Student>(c1);
            Assert.AreEqual(r1.Count() > 0, true);
        }

        [TestMethod]
        public void LoadWithBinaryCriteriaByEndWith()
        {
            BaseCriteria c1 = new BinaryCriteria("LastName", "on", BinaryComparison.EndWith);
            var r1 = Connection.Load<Student>(c1);
            Assert.AreEqual(r1.Count() > 0, true);
        }

        [TestMethod]
        public void LoadWithBinaryCriteriaByLike()
        {
            BaseCriteria c1 = new BinaryCriteria("LastName", "en", BinaryComparison.Like);
            var r1 = Connection.Load<Student>(c1);
            Assert.AreEqual(r1.Count() > 0, true);
        }

        [TestMethod]
        public void LoadWithBinaryCriteriaNotLike()
        {
            BaseCriteria c1 = new BinaryCriteria("LastName", "en", BinaryComparison.NotLike);
            var r1 = Connection.Load<Student>(c1);
            Assert.AreEqual(r1.Count() > 0, true);
        }

        [TestMethod]
        public void LoadWithNullCriteria()
        {
            BaseCriteria c1 = new NullCriteria("Score");
            var r1 = Connection.Load<CourseSelection>(c1);
            Assert.AreEqual(r1.Count() > 0, true);
        }

        [TestMethod]
        public void LoadWithNullCriteriaWithNotCriteria()
        {
            BaseCriteria c1 = new NotCriteria(new NullCriteria("Score"));
            var r1 = Connection.Load<CourseSelection>(c1);
            Assert.AreEqual(r1.Count() > 0, true);
        }

        [TestMethod]
        public void LoadWithBetweenCriteria()
        {
            BaseCriteria c1 = new BetweenCriteria("SelectDate", new DateTime(2013, 1, 2), new DateTime(2013, 1, 3));
            var r1 = Connection.Load<CourseSelection>(c1);
            Assert.AreEqual(r1.Count() > 0, true);
        }

        [TestMethod]
        public void LoadWithBetweenCriteriaWithNotCriteria()
        {
            BaseCriteria c1 = new NotCriteria(
                new BetweenCriteria("SelectDate", new DateTime(2013, 1, 2), new DateTime(2013, 1, 3))
                );
            var r1 = Connection.Load<CourseSelection>(c1);
            Assert.AreEqual(r1.Count() > 0, true);
        }

        [TestMethod]
        public void LoadWithInCriteria()
        {
            BaseCriteria c1 = new InCriteria("Id", new int[] { 1, 4, 7 });
            var r1 = Connection.Load<Student>(c1);
            Assert.AreEqual(r1.Count() > 0, true);

            BaseCriteria c2 = new InCriteria("FirstName", new string[] { "Mary", "Bill", "Ross" });
            var r2 = Connection.Load<Student>(c2);
            Assert.AreEqual(r2.Count() > 0, true);
        }

        [TestMethod]
        public void LoadWithInCriteriaWithNotCriteria()
        {
            BaseCriteria c1 = new NotCriteria(new InCriteria("Id", new int[] { 1, 4, 7 }));
            var r1 = Connection.Load<Student>(c1);
            Assert.AreEqual(r1.Count() > 0, true);

            BaseCriteria c2 = new NotCriteria(new InCriteria("FirstName", new string[] { "Mary", "Bill", "Ross" }));
            var r2 = Connection.Load<Student>(c2);
            Assert.AreEqual(r2.Count() > 0, true);
        }

        //        //eg. select all students who have chosen COMP 101
        //        //select . from [Student] a where EXISTS 
        //        //    (select 1 from [CourseSelection] b where b.Student = a and b.Course.Code = 'COMP 101')
        ////select * from Student a
        ////where exists(select 1 from
        ////    (
        ////    select Student from CourseSelection where Cancelled = 0
        ////    and Course = 1
        ////    )X
        ////);
        [TestMethod]
        public void LoadWithExistsCriteria()
        {
            BaseCriteria c1 = new ExistsCriteria(typeof(CourseSelection),
                new BinaryCriteria("Course.Code", "COMP 102"),
                new ExistsLink[] { 
                    new ExistsLink("Id", "Student")
                }
                );
            var r1 = Connection.Load<Student>(c1);
            Assert.AreEqual(r1.Count() > 0, true);
        }

        [TestMethod]
        public void LoadWithExistsCriteriaWithNotCriteria()
        {
            BaseCriteria c1 = new NotCriteria(new ExistsCriteria(typeof(CourseSelection),
                new BinaryCriteria("Course.Code", "COMP 102"),
                new ExistsLink[] { 
                    new ExistsLink("Id", "Student")
                }
                ));
            var r1 = Connection.Load<Student>(c1);
            Assert.AreEqual(r1.Count() > 0, true);
        }

        [TestMethod]
        public void LoadWithExistsCriteriaWithNotLinks()
        {
            BaseCriteria c1 = new ExistsCriteria(typeof(CourseSelection),
                new BinaryCriteria("Course.Code", "COMP 102"),
                new ExistsLink[] { 
                }
                );
            var r1 = Connection.Load<Student>(c1);
            Assert.AreEqual(r1.Count() > 0, true);
        }

        [TestMethod]
        public void LoadWithExistsCriteriaWithoutInnerCriteria()
        {
            BaseCriteria c1 = new ExistsCriteria(typeof(CourseSelection),
                null,
                new ExistsLink[] { 
                    new ExistsLink("Id", "Student")
                }
                );
            var r1 = Connection.Load<Student>(c1);
            Assert.AreEqual(r1.Count() > 0, true);
        }

        [TestMethod]
        public void LoadWithExistsCriteriaWithoutInnerCriteriaWithoutLinks()
        {
            BaseCriteria c1 = new ExistsCriteria(typeof(CourseSelection),
                null,
                null
                );
            var r1 = Connection.Load<Student>(c1);
            Assert.AreEqual(r1.Count() > 0, true);
        }

        //        //eg. select all students who have chosen at lease two courses
        ////select * from Student a
        ////where EXISTS(select 1 from
        ////    (
        ////    select Student from(
        ////    select Student, Count(1) as cc from CourseSelection where Cancelled = 0 
        ////    group by Student)AA where cc >= 2
        ////    )X
        ////where X.Student = a.Id
        ////);        
        //        [TestMethod]
        //        public void LoadListWithExistsGroupCriteria()
        //        {
        //            //BaseCriteria c1 = new ExistsGroupCriteria(
        //            //    new GroupTable(typeof(CourseSelection), "Student", new GroupTableAggregateColumn("Id", "X")),
        //            //    new GroupTableLink[] 
        //            //    { 
        //            //        new GroupTableLink("Student", "Id") 
        //            //    },
        //            //    new BinaryCriteria("Cancelled", false),
        //            //    null
        //            //    );
        //            //IList<Student> r1 = session.LoadList<Student>(c1);
        //            //Assert.AreEqual(r1.Count > 0, true);
        //        }

        //        [TestMethod]
        //        public void LoadListWithExistsGroupCriteriaWithNotCriteria()
        //        {
        //            //BaseCriteria c1 = new NotCriteria(new ExistsGroupCriteria(
        //            //    new GroupTable(typeof(CourseSelection), "Student", new GroupTableAggregateColumn("Id", "X")),
        //            //    new GroupTableLink[] 
        //            //    { 
        //            //        new GroupTableLink("Student", "Id") 
        //            //    },
        //            //    new BinaryCriteria("Cancelled", false),
        //            //    null
        //            //    ));
        //            //IList<Student> r1 = session.LoadList<Student>(c1);
        //            //Assert.AreEqual(r1.Count > 0, true);
        //        }

        [TestMethod]
        public void LoadWithAddCriteria()
        {
            BaseCriteria c1 = new AndCriteria(
                new BinaryCriteria("LastName", "Geller"),
                new BinaryCriteria("FirstName", "Ross"),
                new BinaryCriteria("StudentId", "300123009")
                );
            var r1 = Connection.Load<Student>(c1);
            Assert.AreEqual(r1.Count() > 0, true);
        }

        [TestMethod]
        public void LoadWithOrCriteria()
        {
            BaseCriteria c1 = new OrCriteria(
                new BinaryCriteria("LastName", "Geller"),
                new BinaryCriteria("FirstName", "Ross"),
                new BinaryCriteria("StudentId", "300123009")
                );
            var r1 = Connection.Load<Student>(c1);
            Assert.AreEqual(r1.Count() > 0, true);
        }

        [TestMethod]
        public void SaveNew()
        {
            Student r1 = new Student() { FirstName = "Test", LastName = "Test", Picture = Image.FromFile(IMAGE_PATH) };
            Connection.Save<Student>(r1);
            Assert.AreEqual(r1.Id > 0, true);

            BaseCriteria c2 = new AndCriteria(
                new BinaryCriteria("LastName", "Test"),
                new BinaryCriteria("FirstName", "Test")
                );
            var r2 = Connection.Load<Student>(c2);
            Assert.AreEqual(r2.Count() > 0, true);
            var s2 = r2.First();
            Assert.AreEqual(s2.Picture != null, true);
        }

        [TestMethod]
        public void SaveExists()
        {
            var r1 = Connection.Load<Student>();
            var c1 = r1.Count();
            foreach (var item in r1)
            {
                item.Birthday = item.Birthday.AddDays(1);
                Connection.Save<Student>(item);
                Assert.AreEqual(item.Id > 0, true);
            }

            var r2 = Connection.Load<Student>();
            var c2 = r2.Count();
            Assert.AreEqual(c1 == c2, true);
        }

        [TestMethod]
        public void DeleteWithoutCriteria()
        {
            BaseCriteria c1 = new BinaryCriteria("FirstName", "Test");

            SaveNew();
            var r2 = Connection.Load<Student>(c1);
            Assert.AreEqual(r2.Count() > 0, true);

            Connection.Delete<Student>(r2);

            var r3 = Connection.Load<Student>(c1);
            Assert.AreEqual(r3.Count() == 0, true);
        }

        [TestMethod]
        public void DeleteWithCriteria()
        {
            SaveNew();

            BaseCriteria c1 = new BinaryCriteria("FirstName", "Test");
            Connection.Delete<Student>(c1);

            var r1 = Connection.Load<Student>(c1);
            Assert.AreEqual(r1.Count() == 0, true);
        }


    }
}
